﻿Imports System.Data.SqlClient
Imports System.IO
Imports System.Data.Odbc

Public Class frmPagobus
    Private admComercial As New AdminPAQ_Comercial

    Dim Bitacora As New clsActividad
    Dim Usuario As New clsUsuarioPrincipal
    Private NombresRutas As New DataTable("Rutas")
    Private CadenasAutocompletado As New AutoCompleteStringCollection

    Private minuto As Integer = 0
    Private segundo As Integer = 0
    Private milisegundo As Integer = 0

    Dim Continuar As Boolean

    Private Sub btnBuscarLiquidador_Click(sender As System.Object, e As System.EventArgs) Handles btnBuscarLiquidador.Click
        Dim oFD As New OpenFileDialog
        With oFD
            .Title = "Seleccionar fichero"
            .Filter = "Ficheros de texto (*.xls;*.xlsx)|*.xls;*.xlsx" & _
                      "|Todos los ficheros (*.*)|*.*"
            .FileName = Me.txtFile.Text
            If .ShowDialog = System.Windows.Forms.DialogResult.OK Then
                Me.txtFile.Text = .FileName
            End If
        End With
        Excel()
    End Sub
    Sub CargaRutas()
        NombresRutas.Columns.Add("Nombre")

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "Select Nombre FROM Rutas Order By Nombre"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Do While rdBuscar.Read()
            NombresRutas.Rows.Add(rdBuscar("Nombre").ToString.Trim)
        Loop

    End Sub
    Private Sub ControlEdicion(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles dgPagobus.EditingControlShowing
        If dgPagobus.CurrentCell.ColumnIndex = 4 Then
            DirectCast(e.Control, TextBox).AutoCompleteMode = AutoCompleteMode.SuggestAppend
            DirectCast(e.Control, TextBox).AutoCompleteSource = AutoCompleteSource.CustomSource
            DirectCast(e.Control, TextBox).AutoCompleteCustomSource = CadenasAutocompletado
        Else
            DirectCast(e.Control, TextBox).AutoCompleteMode = Nothing
        End If

    End Sub
    Private Sub FinDeEdicion(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs) Handles dgPagobus.CellEndEdit
        'Añadir aqui las validaciones necesarias
        Try
            If dgPagobus.CurrentCell.ColumnIndex = 4 Then
                'Dim _Producto As DataRow = NombresProductos.Rows.Find(Me.dgProductos(e.ColumnIndex, e.RowIndex).Value)
                'Me.dgProductos(0, e.RowIndex).Value = _Producto("Nombre")
                dgPagobus.CurrentRow.Cells(3).Value = BuscarIntDatoCitra("Rutas", "IdRuta", "UPPER(Nombre)='" & UCase(dgPagobus.CurrentRow.Cells(4).Value.ToString.Trim) & "'")
                dgPagobus.CurrentRow.Cells(4).Value = BuscarStrDatoCitra("Rutas", "Nombre", "IdRuta=" & UCase(dgPagobus.CurrentRow.Cells(3).Value))

            End If
        Catch ex As Exception
            '.... Accion en caso de excepcion
        End Try
    End Sub

    Private Sub Excel()
        dgPagobus.Rows.Clear()
        Application.DoEvents()

        txtStatus.Text = "Abriendo archivo..."
        Application.DoEvents()


        Dim xl As Object
        Dim wb As Object

        'Crear objeto excel
        xl = CreateObject("Excel.Application")
        xl.Visible = False
        wb = xl.activeworkbook
        wb = xl.Workbooks.Open(txtFile.Text)

        Dim rng As Integer = 5
        Dim col As Integer = 2
        Dim i As Integer = 5
        Dim cont As Integer = 0

        Dim Interno As String = ""
        Dim Autobus As String = ""
        Dim Ruta As String = ""
        Dim Preferencial As Double = 0
        Dim General As Double = 0
        Dim TercerEdad As Double = 0
        Dim ImportePreferencial As Double = 0
        Dim ImporteGeneral As Double = 0
        Dim ImporteTercerEdad As Double = 0
        Dim IdAutobus As Integer = 0
        Dim IdRuta As Integer = 0
        Dim Validaciones As Integer = 0

        For i = 5 To 1000
            Interno = wb.sheets(2).cells(i, 15).value
            If Interno = "" Then
                Exit For
            End If
            Validaciones = wb.sheets(2).cells(i, 8).value
            If Validaciones > 0 Then
                cont = cont + 1
                txtStatus.Text = "Registros procesados: " & cont
                Application.DoEvents()

                IdAutobus = BuscarIntDatoCitra("Autobuses", "IdAutobus", "cCodigoC01='" & Interno & "'")
                Autobus = BuscarStrDatoCitra("Autobuses", "NoEconomico", "IdAutobus=" & IdAutobus)
                Ruta = BuscarStrDatoCitra("SELECT Nombre FROM Rutas R INNER JOIN Autobuses A ON R.IDRUTA=A.IDRUTA Where A.IdAutobus=" & IdAutobus)
                IdRuta = BuscarIntDatoCitra("Rutas", "IdRuta", "Nombre='" & Ruta & "'")
                Preferencial = wb.sheets(2).cells(i, 7).value
                TercerEdad = wb.sheets(2).cells(i, 6).value
                General = wb.sheets(2).cells(i, 5).value
                ImportePreferencial = wb.sheets(2).cells(i, 12).value
                ImporteGeneral = wb.sheets(2).cells(i, 10).value
                ImporteTercerEdad = wb.sheets(2).cells(i, 11).value
                dgPagobus.Rows.Add(IdAutobus, Interno, Autobus, IdRuta, Ruta, Preferencial, General, TercerEdad, ImportePreferencial, ImporteGeneral, ImporteTercerEdad)
                Application.DoEvents()
            End If
        Next

        'cerramos el archivo Excel
        wb.CLOSE(False)
        xl.quit()
        xl = Nothing

        txtStatus.Text = "Listo"
        Application.DoEvents()
    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub

    Private Sub frmPagobus_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        Bitacora.RegistraActividad("Cerró al Módulo de Pagobus")
        Usuario.Ubicacion("Ventana Principal")
        frmMain.statusActividad.Text = ""
        NombresRutas.PrimaryKey = Nothing
        NombresRutas.Clear()
        NombresRutas.Columns.Clear()

    End Sub

    Private Sub frmPagobus_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System
        frmMain.statusActividad.Text = ": Módulo de Pagobus"
        txtStatus.Text = ""

        Bitacora.RegistraActividad("Ingresó al Módulo de Pagobus")
        Usuario.Ubicacion("Módulo de Pagobus")

        ProgressBar1.Visible = False
        dgPagobus.Rows.Clear()

        CargaRutas()
        Dim Claves(0) As DataColumn
        Claves(0) = NombresRutas.Columns("Nombre")
        NombresRutas.PrimaryKey = Claves

        For Each Nombre In NombresRutas.Rows
            CadenasAutocompletado.Add(Nombre("Nombre"))
        Next

        dgPagobus.Columns(5).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgPagobus.Columns(6).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgPagobus.Columns(7).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        dgPagobus.Columns(8).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight

    End Sub
    Sub Guardar()
        Dim Pago As New clsPagobus

        ProgressBar1.Visible = True

        With ProgressBar1
            .Minimum = 0
            .Maximum = dgPagobus.Rows.Count
        End With

        For i = 0 To dgPagobus.Rows.Count - 1
            txtStatus.Text = "Progreso"
            ProgressBar1.Value = i + 1
            Application.DoEvents()

            Pago.Fecha = datFecha.Value
            Pago.IdAutobus = dgPagobus.Rows(i).Cells(0).Value
            Pago.NoEconomico = BuscarStrDatoCitra("SELECT NoEconomico FROM Autobuses WHERE IdAutobus=" & Pago.IdAutobus)
            Pago.IdRuta = dgPagobus.Rows(i).Cells(3).Value
            Pago.NombreRuta = dgPagobus.Rows(i).Cells(4).Value.ToString.Trim
            Pago.Preferencial = dgPagobus.Rows(i).Cells(5).Value
            Pago.General = dgPagobus.Rows(i).Cells(6).Value
            Pago.TerceraEdad = dgPagobus.Rows(i).Cells(7).Value
            Pago.Validaciones = dgPagobus.Rows(i).Cells(5).Value + dgPagobus.Rows(i).Cells(6).Value + dgPagobus.Rows(i).Cells(7).Value
            Pago.IngresoPreferencial = dgPagobus.Rows(i).Cells(8).Value
            Pago.IngresoGeneral = dgPagobus.Rows(i).Cells(9).Value
            Pago.ImporteTerceraEdad = dgPagobus.Rows(i).Cells(10).Value
            Pago.IngresoValidaciones = dgPagobus.Rows(i).Cells(8).Value + dgPagobus.Rows(i).Cells(9).Value + dgPagobus.Rows(i).Cells(10).Value
            Pago.Importe01 = 0
            Pago.Importe02 = 0
            Pago.Importe03 = 0
            Pago.Importe04 = 0
            Pago.Importe05 = 0
            Pago.Texto01 = ""
            Pago.Texto02 = ""
            Pago.Texto03 = ""
            Pago.Texto04 = ""
            Pago.Texto05 = ""
            Pago.Insertar()
        Next
        Bitacora.RegistraActividad("Registró pagobus")
        dgPagobus.Rows.Clear()
        ProgressBar1.Visible = False
        txtStatus.Text = ""

    End Sub

    Private Sub ToolStripButton5_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton5.Click
        Guardar()

    End Sub

    Private Sub GuardarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles GuardarToolStripMenuItem.Click
        Guardar()

    End Sub

    Private Sub NuevaToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles NuevaToolStripMenuItem.Click
        dgPagobus.Rows.Clear()
        txtFile.Clear()
        txtStatus.Text = ""
        datFecha.Value = Now()
        ToolStripButton5.Enabled = True
        ToolStripButton4.Enabled = True
        GuardarToolStripMenuItem.Enabled = True
        EliminarToolStripMenuItem.Enabled = True
        CerrarDiaToolStripMenuItem.Enabled = True
    End Sub

    Private Sub LiquidaciónToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles LiquidaciónToolStripMenuItem.Click
        frmRPagobusDetallado.ShowDialog()

    End Sub

    Private Sub AccionesToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles AccionesToolStripMenuItem.Click

    End Sub
    Sub CerrarDia()
        'Verifica que todas las rutas tengan asignado el concepto de Ventas
        Dim Rutas As Integer = BuscarIntDatoCitra("Select count(*) FROM Rutas")
        Dim RutasAsignadas As Integer = BuscarIntDatoCitra("Select count(*) from Conceptos where Tipo='Pagobus'")
        Dim Faltan As Integer = Rutas - RutasAsignadas
        If Faltan > 0 Then
            MsgBox("No se puede cerrar el dia porque faltan " & Faltan & " rutas por asignar en Rutas > Conceptos", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If

        'Verifica que todas las rutas tengan asignado el cliente para envios
        Dim RutasConClientes As Integer = BuscarIntDatoCitra("Select count(*) from Rutas where Texto01<>''")
        Dim FaltanClientes As Integer = Rutas - RutasConClientes
        If FaltanClientes > 0 Then
            MsgBox("No se puede cerrar el dia poorque faltan " & FaltanClientes & " clientes para envio por asignar en el Catálogo de Rutas", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If

        'Carga Libreria
        txtStatus.Text = "Cargando librerias..."
        Application.DoEvents()

        Dim RutaDLL As String = ""
        Dim cCodigoPre As String = ""
        Dim cCodigoGen As String = ""
        Dim cCodigoASE As String = ""

        Try

            Dim key As Microsoft.Win32.RegistryKey
            key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(gsRegPath)
            RutaDLL = CType(key.GetValue("RutaDLL"), String)
            cCodigoGen = CType(key.GetValue("cCodigoPGen"), String)
            cCodigoPre = CType(key.GetValue("cCodigoPPre"), String)
            cCodigoASE = CType(key.GetValue("cCodigoASE"), String)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
            MsgBox("Se interrumpio el proceso", MsgBoxStyle.Exclamation, "Aviso")
        End Try

        If Usa_AdminPAQ = True Then
            'ENTONCES SE USA EL SDK DE ADMINPAQ
            txtStatus.Text = "Cargando librerias..."
            Application.DoEvents()

            'Carga SDK
            Dim lError As Integer
            txtStatus.Text = "Cargando SDK de Adminpaq..."
            Application.DoEvents()

            Try
                Directory.SetCurrentDirectory(RutaDLL)
                lError = SDK_AdminPAQ.fInicializaSDK
                If lError <> 0 Then
                    MensajeError(lError)
                    End
                End If
            Catch ex As Exception
                txtStatus.Text = ex.Message
                MsgBox("No se pudo cargar el SDK de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End Try

            'Abre Empresa de AdminPAQ
            txtStatus.Text = "Abriendo empresa de AdminPAQ..."
            Application.DoEvents()

            Try
                lError = SDK_AdminPAQ.fAbreEmpresa(Comercial)
                If lError <> 0 Then
                    MensajeError(lError)
                End If
            Catch ex As Exception
                MsgBox("No se pudo abrir la empresa de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End Try

        Else
            'ENTONCES SE USA EL DE COMERCIAL
            'Carga Libreria
            txtStatus.Text = "Cargando SDK de ContPAQ I Comercial"
            Application.DoEvents()
            Dim lRet As Long
            lRet = fSDK_Inicializa(Comercial)
            If lRet <> 0 Then
                MsgBox("No pudo ser iniciado el SDK de Comercial", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If

        End If

        FilePath = Application.StartupPath & "\Pagobus_Bitacora" & Now.Year & Now.Month & Now.Day & "_" & Now.Hour & Now.Minute & ".txt"
        strStreamW = File.OpenWrite(FilePath)
        strStreamWriter = New StreamWriter(strStreamW, _
                            System.Text.Encoding.UTF8)

        Dim Dias As Integer = DateDiff(DateInterval.Day, datFecha.Value, datFecha2.Value)

        If Dias < 0 Then
            MsgBox("El rango de fechas es incorrecto", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        ElseIf Dias = 0 Then
            If DiaTrabajadoPagobus(datFecha.Value) = True Then
                MsgBox("El dia " & datFecha.Text & " ya ha sido archivado", MsgBoxStyle.Exclamation, "Aviso")
                Exit Sub
            End If
            'AQUI TAMBIEN
            Comienza(cCodigoGen, cCodigoPre, cCodigoASE)
        ElseIf Dias > 0 Then
            datFecha.Value = datFecha.Value.AddDays(-1)
            Application.DoEvents()
            For i = 0 To Dias
                datFecha.Value = datFecha.Value.AddDays(1)
                Application.DoEvents()
                If Not (DiaTrabajadoPagobus(datFecha.Value) = True) Then
                    'AQUI SUCEDE TODO
                    Comienza(cCodigoGen, cCodigoPre, cCodigoASE)
                End If
            Next
        End If


        strStreamWriter.Close()
        strStreamW.Close()


        MsgBox("Los dias se han cerrado correctamente", MsgBoxStyle.Information, "Aviso")

        'Cierra el SDK
        'lblStatusDia.Text = "Cerrando SDK de AdminPAQ..."
        'Application.DoEvents()

        '     Try
        '   SDK_AdminPAQ.fCierraEmpresa()
        '  SDK_AdminPAQ.fTerminaSDK()
        '   Catch ex As Exception
        ' MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        ' MsgBox("No se pudo cerrar el SDK de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
        '  End Try

        txtStatus.Text = ""
        Application.DoEvents()
        Close()



    End Sub
    Private strStreamW As Stream
    Private strStreamWriter As StreamWriter
    Private FilePath As String
    Sub Comienza(ByVal General As String, ByVal Preferencial As String, ByVal Almacen As String)
        'Genera Informacion para Envio
        Dim cnconn As New SqlConnection
        Dim strSql As String
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Dim FechaLiquidacion As String = datFecha.Value.ToString("MM/dd/yyyy")

        Try
            txtStatus.Text = "Buscando pagobus..."
            Application.DoEvents()

            cnconn.ConnectionString = CitraConnection
            strSql = "Select IdRuta,SUM(IngresoPreferencial) as Preferencial FROM Pagobus WHERE convert(varchar,fecha,101)='" & FechaLiquidacion & "' GROUP BY IdRuta"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Dim CodigoCliente As String = 0
            Dim CodigoRuta As String = ""
            Dim CodigoConepto As String = ""
            Dim Fecha As Date = Now()
            Dim AdminPAQ As New clsAdminPAQ
            Dim Dia As New clsDiaTrabajoPagobus
            Dim Cont As Integer = 0
            Dim NombreRuta As String = ""
            Dim Resultado As String = ""

            Do While rdBuscar.Read()
                'Primero busca cliente para Envios
                txtStatus.Text = "Enviando registros preferenciales a AdminPAQ..."
                Application.DoEvents()

                CodigoCliente = BuscarStrDatoCitra("Rutas", "Texto01", "IdRuta=" & rdBuscar("IdRuta"))
                CodigoRuta = BuscarStrDatoCitra("Rutas", "cCodigoV01", "IdRuta=" & rdBuscar("IdRuta"))
                'Busca Concepto
                CodigoConepto = BuscarStrDatoCitra("Conceptos", "CodigoConcepto", "CodigoRuta='" & CodigoRuta & "' AND Tipo='Pagobus'")
                Fecha = datFecha.Value
                NombreRuta = BuscarStrDatoCitra("Rutas", "Nombre", "IdRuta=" & rdBuscar("IdRuta"))

                If Usa_AdminPAQ = True Then
                    Resultado = AdminPAQ.EnviaAdminPAQ(Fecha, CodigoConepto, BuscarIntDatoComercial("SELECT CNOFOLIO FROM MGW10006 WHERE CCODIGOC01='" & CodigoConepto & "'") + 1, "PBS", CodigoCliente, NombreRuta, "", Preferencial, Almacen, 1, rdBuscar("Preferencial"))
                    If Mid(Resultado, 1, 2) = "NO" Then
                        strStreamWriter.WriteLine(Fecha & ":" & NombreRuta & ":" & Mid(Resultado, 3))
                    End If

                Else
                    admComercial.EnviaComercial(Fecha, CodigoConepto, BuscarIntDatoComercial("SELECT CNOFOLIO FROM MGW10006 WHERE CCODIGOC01='" & CodigoConepto & "'") + 1, "PBS", CodigoCliente, NombreRuta, "", Preferencial, Almacen, 1, rdBuscar("Preferencial"), AdminPAQ_Comercial.TIPO_DOCUMENTO_COMERCIAL.Ventas)
                End If

                Cont = Cont + 1
            Loop

            rdBuscar.Close()
            cnconn.Close()

            '**************************PREFERENCIAL************************************

            txtStatus.Text = "Buscando pagobus..."
            Application.DoEvents()

            cnconn.ConnectionString = CitraConnection
            strSql = "Select IdRuta,SUM(IngresoGeneral) as General FROM Pagobus WHERE convert(varchar,fecha,101)='" & FechaLiquidacion & "' GROUP BY IdRuta"

            cmdBuscar.Connection = cnconn
            cmdBuscar.CommandText = strSql

            cnconn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            Do While rdBuscar.Read()
                'Primero busca cliente para Envios
                txtStatus.Text = "Enviando registros generales a AdminPAQ..."
                Application.DoEvents()

                CodigoCliente = BuscarStrDatoCitra("Rutas", "Texto01", "IdRuta=" & rdBuscar("IdRuta"))
                CodigoRuta = BuscarStrDatoCitra("Rutas", "cCodigoV01", "IdRuta=" & rdBuscar("IdRuta"))
                'Busca Concepto
                CodigoConepto = BuscarStrDatoCitra("Conceptos", "CodigoConcepto", "CodigoRuta='" & CodigoRuta & "' AND Tipo='Pagobus'")
                Fecha = datFecha.Value
                NombreRuta = BuscarStrDatoCitra("Rutas", "Nombre", "IdRuta=" & rdBuscar("IdRuta"))


                If Usa_AdminPAQ = True Then
                    AdminPAQ.EnviaAdminPAQ(Fecha, CodigoConepto, BuscarIntDatoComercial("SELECT CNOFOLIO FROM MGW10006 WHERE CCODIGOC01='" & CodigoConepto & "'") + 1, "PBS", CodigoCliente, NombreRuta, "", General, Almacen, 1, rdBuscar("General"))
                Else
                    admComercial.EnviaComercial(Fecha, CodigoConepto, BuscarIntDatoComercial("SELECT CNOFOLIO FROM MGW10006 WHERE CCODIGOC01='" & CodigoConepto & "'") + 1, "PBS", CodigoCliente, NombreRuta, "", General, Almacen, 1, rdBuscar("General"), AdminPAQ_Comercial.TIPO_DOCUMENTO_COMERCIAL.Ventas)
                End If

                Cont = Cont + 1
            Loop

            If Cont = 0 Then
                MsgBox("No se encontraron registros para procesar", MsgBoxStyle.Exclamation, "Aviso")
            Else
                Dia.Dia = Fecha.Day
                Dia.Mes = Fecha.Month
                Dia.Anio = Fecha.Year
                Dia.Insertar()
                'MsgBox("El dia se ha cerrado correctamente", MsgBoxStyle.Information, "Aviso")
                Bitacora.RegistraActividad("Cerró el Pagobus del dia " & datFecha.Text)
            End If


        Catch ex As SqlException
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally
            If cnconn.State = ConnectionState.Open Then
                cnconn.Close()
            End If
        End Try

    End Sub
    Private Sub CerrarDiaToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles CerrarDiaToolStripMenuItem.Click
        CerrarDia()
    End Sub

    Private Sub ReAbrirDiaToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles ReAbrirDiaToolStripMenuItem.Click

        ReAbrir()

    End Sub
    Sub ReAbrir()
        'Pide contraseña de reactivacion
        frmPassword.ShowDialog()
        If frmPassword.txtPass.Text.Trim <> "Ausl4321PAGOBUS" Then
            MsgBox("La contraseña es incorrecta", MsgBoxStyle.Critical, "Aviso")
            Exit Sub
        End If

        'Carga Libreria
        txtStatus.Text = "Cargando librerias..."
        Application.DoEvents()

        Dim RutaDLL As String = ""
        Dim cCodigoPre As String = ""
        Dim cCodigoGen As String = ""
        Dim cCodigoASE As String = ""

        Try

            Dim key As Microsoft.Win32.RegistryKey
            key = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(gsRegPath)
            RutaDLL = CType(key.GetValue("RutaDLL"), String)
            cCodigoGen = CType(key.GetValue("cCodigoPGen"), String)
            cCodigoPre = CType(key.GetValue("cCodigoPPre"), String)
            cCodigoASE = CType(key.GetValue("cCodigoASE"), String)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
            MsgBox("Se interrumpio el proceso", MsgBoxStyle.Exclamation, "Aviso")
        End Try

        'Carga SDK
        Dim lError As Integer
        txtStatus.Text = "Cargando SDK de Adminpaq..."
        Application.DoEvents()

        Try
            Directory.SetCurrentDirectory(RutaDLL)
            lError = SDK_AdminPAQ.fInicializaSDK
            If lError <> 0 Then
                MensajeError(lError)
                End
            End If
        Catch ex As Exception
            MsgBox("No se pudo cargar el SDK de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End Try

        'Abre Empresa de AdminPAQ
        txtStatus.Text = "Abriendo empresa de AdminPAQ..."
        Application.DoEvents()

        Try
            lError = SDK_AdminPAQ.fAbreEmpresa(Comercial)
            If lError <> 0 Then
                MensajeError(lError)
            End If
        Catch ex As Exception
            MsgBox("No se pudo abrir la empresa de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End Try

        Continuar = False
        ImportaTablas(1)
        Timer1.Enabled = True
        Application.DoEvents()


        Do While Continuar = False
            txtStatus.Text = "Espere mientras se actualizan las tablas..."
            Application.DoEvents()
        Loop

        'Primero trae los codigos de Ruta
        'Variables para busqueda de rutas
        Dim cnConnCitra As New SqlConnection
        Dim strSqlCitra As String = ""
        Dim cmdBuscarCitra As New SqlCommand
        Dim rdBuscarCitra As SqlDataReader
        Dim Concepto As String = ""
        Dim CadenaConceptos As String = ""
        Dim cIDConce01 As Integer = 0

        'Arma conceptos
        Try
            txtStatus.Text = "Buscando conceptos..."
            Application.DoEvents()

            cnConnCitra.ConnectionString = CitraConnection
            strSqlCitra = "Select CodigoConcepto FROM Conceptos Where Tipo='Pagobus' GROUP BY CodigoConcepto"
            cmdBuscarCitra.Connection = cnConnCitra
            cmdBuscarCitra.CommandText = strSqlCitra
            cnConnCitra.Open()
            rdBuscarCitra = cmdBuscarCitra.ExecuteReader

            Do While rdBuscarCitra.Read()
                'Por Cada Ruta Buscar Registros en conceptos
                Concepto = rdBuscarCitra("CodigoConcepto")
                cIDConce01 = BuscarStrDatoCitra("MGW10006", "cidConce01", "cCodigoC01='" & Concepto & "'")
                CadenaConceptos = CadenaConceptos & "," & cIDConce01
            Loop
            CadenaConceptos = Mid(CadenaConceptos, 2)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnConnCitra.State = ConnectionState.Open Then
                cnConnCitra.Close()
            End If
        End Try

        Dim FechaLiquidacion As String = datFecha.Value.ToString("MM/dd/yyyy")
        Dim Fecha As Date = datFecha.Value
        Dim DT As New clsDiaTrabajoPagobus
        Dim cnconnAdmin As New SqlConnection
        Dim strSqlAdmin As String = ""
        Dim cmdBuscarAdmin As New SqlCommand
        Dim rdBuscarAdmin As SqlDataReader
        Dim cidDocum01 As Integer = 0

        Try
            txtStatus.Text = "Buscando registros en AdminPAQ..."
            Application.DoEvents()
            cnconnAdmin.ConnectionString = CitraConnection
            strSqlAdmin = "SELECT cIdDocum01 FROM MGW10008 WHERE CIDCONCE01 IN(" & CadenaConceptos & ") AND CONVERT(VARCHAR,CFECHA,101)='" & FechaLiquidacion & "'"
            cmdBuscarAdmin.Connection = cnconnAdmin
            cmdBuscarAdmin.CommandText = strSqlAdmin
            cnconnAdmin.Open()

            rdBuscarAdmin = cmdBuscarAdmin.ExecuteReader

            Do While rdBuscarAdmin.Read()
                txtStatus.Text = "Borrando Registros..."
                Application.DoEvents()

                cidDocum01 = rdBuscarAdmin("cidDocum01")
                BorraDocumento(cidDocum01)
            Loop

        Catch ex As SqlException
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        Finally
            If cnconnAdmin.State = ConnectionState.Open Then
                cnconnAdmin.Close()
            End If
        End Try

        DT.Dia = Fecha.Day
        DT.Mes = Fecha.Month
        DT.Anio = Fecha.Year
        DT.Borrar()

        'Cierra el SDK
        txtStatus.Text = "Cerrando SDK de AdminPAQ..."
        Application.DoEvents()

        Try
            SDK_AdminPAQ.fCierraEmpresa()
            SDK_AdminPAQ.fTerminaSDK()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
            MsgBox("No se pudo cerrar el SDK de AdminPAQ", MsgBoxStyle.Exclamation, "Aviso")
        End Try

        txtStatus.Text = ""
        Application.DoEvents()


        MsgBox("El dia se ha reactivado", MsgBoxStyle.Information, "Aviso")
        Bitacora.RegistraActividad("Re-abrió el Pagobus del dia " & datFecha.Text)
    End Sub
    Sub BorraDocumento(ByVal IDDocto As Integer)
        Dim lRet As Long

        lRet = SDK_AdminPAQ.fBuscarIdDocumento(IDDocto)
        If lRet <> 0 Then
            MensajeError(lRet)
        Else
            SDK_AdminPAQ.fBorraDocumento()
        End If
    End Sub

    Private Sub EliminarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles EliminarToolStripMenuItem.Click
        Eliminar()

    End Sub
    Sub Eliminar()
        If DiaTrabajadoPagobus(datFecha.Value) = True Then
            MsgBox("El dia " & datFecha.Text & " ya ha sido archivado", MsgBoxStyle.Exclamation, "Aviso")
            Exit Sub
        End If
        Dim FechaLiquidacion As String = datFecha.Value.ToString("MM/dd/yyyy")

        Call SetSqlCitra("DELETE FROM Pagobus WHERE convert(varchar,fecha,101)='" & FechaLiquidacion & "'")
        dgPagobus.Rows.Clear()
        MsgBox("Registros Eliminados", MsgBoxStyle.Information, "Aviso")
        Bitacora.RegistraActividad("Eliminó el Pagobus del dia " & datFecha.Text)
    End Sub

    Private Sub ToolStripButton4_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton4.Click
        Eliminar()

    End Sub
    Sub Buscar()
        If DiaTrabajadoPagobus(datFecha.Value) = True Then
            ToolStripButton5.Enabled = False
            ToolStripButton4.Enabled = False
            GuardarToolStripMenuItem.Enabled = False
            EliminarToolStripMenuItem.Enabled = False
            CerrarDiaToolStripMenuItem.Enabled = False
        Else
            ToolStripButton5.Enabled = True
            ToolStripButton4.Enabled = True
            GuardarToolStripMenuItem.Enabled = True
            EliminarToolStripMenuItem.Enabled = True
            CerrarDiaToolStripMenuItem.Enabled = True

        End If

        dgPagobus.Rows.Clear()

        Dim COnt As Integer = 0
        Dim FechaLiquidacion As String = datFecha.Value.ToString("MM/dd/yyyy")
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim strSql As String = ""
        strSql = "SELECT Pagobus.Fecha,Pagobus.IdAutobus,Pagobus.NoEconomico,Autobuses.cCodigoC01,Pagobus.IdRuta,Pagobus.NombreRuta,Preferencial,General,IngresoPreferencial,IngresoGeneral FROM Pagobus INNER JOIN Autobuses ON Pagobus.IdAutobus=Autobuses.IdAutobus WHERE CONVERT(VARCHAR,Fecha,101)='" & FechaLiquidacion & "'"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader
        Do While rdBuscar.Read()
            COnt = COnt + 1
            dgPagobus.Rows.Add(rdBuscar("IdAutobus"), rdBuscar("cCodigoC01").ToString.Trim, rdBuscar("NoEconomico").ToString.Trim, rdBuscar("IdRuta"), rdBuscar("NombreRuta").ToString.Trim, rdBuscar("Preferencial"), rdBuscar("General"), rdBuscar("IngresoPreferencial"), rdBuscar("IngresoGeneral"))
        Loop
        If COnt = 0 Then
            MsgBox("No hay registros en el dia seleccionado", MsgBoxStyle.Information, "Aviso")
        End If
    End Sub

    Private Sub ToolStripButton3_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton3.Click
        Buscar()

    End Sub

    Private Sub BuscarToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles BuscarToolStripMenuItem.Click
        Buscar()

    End Sub

    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        milisegundo += 1
        If milisegundo = 9 Then
            milisegundo = 0
            segundo += 1

            If segundo = 5 Then
                Application.DoEvents()
                If BuscarIntDatoCitra("SELECT Resultado FROM EstadoImportacion WHERE IdImportacion=1") = 0 Then
                    Continuar = True
                    Timer1.Enabled = False
                    Application.DoEvents()
                Else
                    segundo = 1
                End If
            End If
        End If
    End Sub
End Class